package kr.or.kead.service;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.InfoStudent;
import kr.or.kead.domain.Professor;
import kr.or.kead.utils.MySqlCon;

public class DaoInfoStudent implements DaoTable {
	
	@Override
	public int insertDao(Object obj) {
		Connection con = MySqlCon.getConn();
		
		
		InfoStudent std = (InfoStudent) obj;
		System.out.println(std);
		String sql = "insert into  infostudent (idx, stdname, juminnum, startdate, enddate, mobile, tel, stdaddr, roomnum, stdtype, grade, email, departcode, password) values(null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, std.getStdname());
			pstmt.setString(2, std.getJuminNum());
			pstmt.setDate(3, new Date(std.getStartDate().getTime()));
			pstmt.setDate(4, new Date(std.getEndDate().getTime()));
			pstmt.setString(5, std.getMobile());
			pstmt.setString(6, std.getTel());
			pstmt.setString(7, std.getStdAddr());
			pstmt.setInt(8, std.getRoomNum());
			pstmt.setInt(9, std.getStdType());
			pstmt.setInt(10, std.getGrade());
			pstmt.setString(11, std.getEmail());
			pstmt.setInt(12, std.getDepart());
			pstmt.setString(13, std.getEmail());
			
			System.out.println(pstmt);
			pstmt.execute();
		} catch (SQLException e) {
			e.getStackTrace();
			return -1;
		} finally
		{
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int updateDao(Object obj) {
		Connection con = MySqlCon.getConn();
		
		InfoStudent std = (InfoStudent) obj;
		
		String sql = "update infostudent set "
				+ "stdname=?,"
				+ "juminNum=?,"
				+ "startdate=?,"
				+ "enddate=?,"
				+ "mobile=?,"
				+ "tel=?,"
				+ "stdAddr=?,"
				+ "roomNum=?,"
				+ "stdtype=?,"
				+ "grade=?,"
				+ "email=?,"
				+ "departcode=?"
				+ " where idx=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, std.getStdname());
			pstmt.setString(2, std.getJuminNum());
			pstmt.setDate(3, new Date(std.getStartDate().getTime()));
			pstmt.setDate(4, new Date(std.getEndDate().getTime()));
			pstmt.setString(5, std.getMobile());
			pstmt.setString(6, std.getTel());
			pstmt.setString(7, std.getStdAddr());
			pstmt.setInt(8, std.getRoomNum());
			pstmt.setInt(9, std.getStdType());
			pstmt.setInt(10, std.getGrade());
			pstmt.setString(11, std.getEmail());
			pstmt.setInt(12, std.getDepart());
			pstmt.setInt(13, std.getIdx());
			
			
			pstmt.executeUpdate();
			System.out.println(pstmt);
		} catch (SQLException e) {
			return -1;
		} finally
		{
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		
		return 0;
	}

	@Override
	public int deleteDao(int regNo) {
		Connection con = MySqlCon.getConn();
		
		
				
		String sql = "delete from infostudent where idx = ?";
		
		try {
			
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, regNo);
			
			System.out.println(pstmt);
			pstmt.executeUpdate();
			return 0;
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
		
		
		
	}

	@Override
	public ArrayList<Object> selectDao() {
		Connection con = MySqlCon.getConn();
		
		
			
			String sql = "select * from infostudent";
			ArrayList<Object> students = new ArrayList<>();
			
			try {
				
				PreparedStatement pstmt = con.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
				InfoStudent std;
				while (rs.next()){
					std = new InfoStudent();
					std.setIdx(rs.getInt("idx"));
					std.setStdname(rs.getString("stdName"));
					std.setJuminNum(rs.getString("juminnum"));
					std.setStartDate(rs.getDate("startdate"));
					std.setEndDate(rs.getDate("enddate"));
					std.setMobile(rs.getString("mobile"));
					std.setTel(rs.getString("tel"));
					std.setStdAddr(rs.getString("stdaddr"));
					std.setRoomNum(rs.getInt("roomnum"));
					std.setStdType(rs.getInt("stdtype"));
					std.setGrade(rs.getInt("grade"));
					std.setEmail(rs.getString("email"));
					std.setDepart(rs.getInt("departCode"));
					students.add(std);
				}
				return students;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
		
		
	}

	@Override
	public Object selectTable(int stdId) {
		Connection con = MySqlCon.getConn();
		
		
		String sql = "select * from infostudent where idx = ?";
		
		try {
				PreparedStatement pstmt = con.prepareStatement(sql);		
				pstmt.setInt(1, stdId);
				
				
				ResultSet rs = pstmt.executeQuery();
				
				if(rs.next())
				{
					InfoStudent std = new InfoStudent();
					
					std.setIdx(rs.getInt("idx"));
					std.setStdname(rs.getString("stdname"));
					std.setJuminNum(rs.getString("juminNum"));
					std.setStartDate(rs.getDate("startdate"));
					std.setEndDate(rs.getDate("enddate"));
					std.setMobile(rs.getString("mobile"));
					std.setTel(rs.getString("tel"));
					std.setStdAddr(rs.getString("stdaddr"));
					std.setRoomNum(rs.getInt("roomNum"));
					std.setStdType(rs.getInt("stdType"));
					std.setGrade(rs.getInt("grade"));
					std.setEmail(rs.getString("email"));
					std.setDepart(rs.getInt("departCode"));
					
					System.out.println(std);
					return std;
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
				
		return null;
	}

	@Override
	public ArrayList<String> selectTableAllList() {
		Connection con = MySqlCon.getConn();
			
		String sql = "select idx,stdname from infostudent";
		ArrayList<String> lists = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {			
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				lists.add(rs.getInt(1)+":"+rs.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return lists;
	}
	public String selectPassWordByEmail(String email){
		Connection con = MySqlCon.getConn();
		String sql = "select password from infostudent where email = ?";
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return null;
	}

	@Override
	public Object selectTableByEmail(String email) {
		Connection con = MySqlCon.getConn();
		
		String sql = "select idx, stdname, juminnum, startdate, enddate, mobile, tel, stdaddr, roomnum, stdtype, grade, email, departcode from infostudent where email = ?";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, email);
			
			rs = pstmt.executeQuery();
			if(rs.next()){
				InfoStudent std = new InfoStudent();
				std.setIdx(rs.getInt("idx"));
				std.setStdname(rs.getString("stdname"));
				std.setJuminNum(rs.getString("juminNum"));
				std.setStartDate(rs.getDate("startdate"));
				std.setEndDate(rs.getDate("enddate"));
				std.setMobile(rs.getString("mobile"));
				std.setTel(rs.getString("tel"));
				std.setStdAddr(rs.getString("stdaddr"));
				std.setRoomNum(rs.getInt("roomNum"));
				std.setStdType(rs.getInt("stdType"));
				std.setGrade(rs.getInt("grade"));
				std.setEmail(rs.getString("email"));
				std.setDepart(rs.getInt("departCode"));
				System.out.println(std);
				return std;
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return null;
	}

	
	@Override
	public int updatePasswd(String pass, String email) {
		Connection con = MySqlCon.getConn();
		Professor professor = new Professor();		
		String sql = "update infostudent set password=? where email = ? ";
				
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, pass);
			pstmt.setString(2, email);
			System.out.println(pstmt);			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally{
			try {pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return 0;
	}
}
	

